package com.hui.dbmigrator.handler;

import com.hui.dbmigrator.config.DatabaseConfig;
import com.hui.dbmigrator.entity.TableDefinition;
import com.hui.dbmigrator.service.TypeConversionService;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Component
@RequiredArgsConstructor
public class OracleHandler extends AbstractDatabaseHandler implements DatabaseHandler {

    private final TypeConversionService typeConversionService;

    @Override
    public String getDatabaseType() {
        return "Oracle";
    }

    @Override
    public String generateCreateTable(TableDefinition table) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(quoteIdentifier(table.getName())).append(" (\n");

        // Columns
        List<String> columnDefs = table.getColumns().stream()
                .map(this::generateColumnDefinition)
                .collect(Collectors.toList());

        // Primary Keys
        String pkColumns = table.getColumns().stream()
                .filter(TableDefinition.ColumnDefinition::isPrimaryKey)
                .map(col -> quoteIdentifier(col.getName()))
                .collect(Collectors.joining(", "));

        if (!pkColumns.isEmpty()) {
            columnDefs.add("  CONSTRAINT PK_" + table.getName() + " PRIMARY KEY (" + pkColumns + ")");
        }

        sql.append(String.join(",\n", columnDefs));
        sql.append("\n)");

        // Oracle 表空间等附加选项可以在这里添加
        if (StringUtils.isNotBlank(table.getTablespace())) {
            sql.append(" TABLESPACE ").append(table.getTablespace());
        }

        // 单独处理注释
        appendComments(sql, table);

        return sql.toString();
    }

    /**
     * 生成Oracle列定义SQL片段
     */
    private String generateColumnDefinition(TableDefinition.ColumnDefinition column) {
        StringBuilder def = new StringBuilder("  ");
        def.append(quoteIdentifier(column.getName())).append(" ").append(column.getType());

        // NULL/NOT NULL
        if (!column.isNullable()) {
            def.append(" NOT NULL");
        }

        // 默认值 (Oracle需要特殊处理SYSDATE和序列)
        if (column.getDefaultValue() != null) {
            String defaultValue = column.getDefaultValue().toString();
            if ("CURRENT_TIMESTAMP".equalsIgnoreCase(defaultValue)) {
                def.append(" DEFAULT SYSDATE");
            } else if (defaultValue.startsWith("SEQ_") && defaultValue.endsWith(".NEXTVAL")) {
                def.append(" DEFAULT ").append(defaultValue);
            } else {
                def.append(" DEFAULT ").append(formatDefaultValue(column.getDefaultValue(), column.getType()));
            }
        }

        return def.toString();
    }

    /**
     * Oracle需要单独执行COMMENT语句
     */
    private void appendComments(StringBuilder sql, TableDefinition table) {
        // 表注释
        if (StringUtils.isNotBlank(table.getComment())) {
            sql.append("\nCOMMENT ON TABLE ").append(quoteIdentifier(table.getName()))
                    .append(" IS '").append(escapeSqlString(table.getComment())).append("'");
        }

        // 列注释
        table.getColumns().stream()
                .filter(col -> StringUtils.isNotBlank(col.getComment()))
                .forEach(col -> {
                    sql.append("\nCOMMENT ON COLUMN ")
                            .append(quoteIdentifier(table.getName())).append(".").append(quoteIdentifier(col.getName()))
                            .append(" IS '").append(escapeSqlString(col.getComment())).append("'");
                });
    }

    /**
     * Oracle标识符引用（大写+双引号）
     */
    @Override
    protected String quoteIdentifier(String identifier) {
        return "\"" + identifier.toUpperCase() + "\"";
    }

    /**
     * Oracle特定的默认值格式化
     */
    @Override
    protected String formatDefaultValue(Object value, String columnType) {
        if (value == null) return "";

        // 处理字符串类型
        if (columnType.startsWith("VARCHAR") || columnType.startsWith("CHAR") ||
                columnType.startsWith("CLOB") || columnType.startsWith("DATE")) {
            return "'" + value.toString().replace("'", "''") + "'";
        }

        // 处理数值类型
        return value.toString();
    }

    // 其他必须实现的方法...
    @Override
    public String generateAlterTable(String tableName, List<String> alterStatements) {
        return "ALTER TABLE " + quoteIdentifier(tableName) + " " +
                String.join(", ", alterStatements);
    }

    @Override
    public TableDefinition extractTable(String tableName, DatabaseConfig config) throws SQLException {
        // Oracle元数据提取实现
        TableDefinition tableDef = new TableDefinition();
        tableDef.setName(tableName);

        try (Connection conn = getConnection(config);
             ResultSet rs = conn.getMetaData().getColumns(null,
                     config.getUsername().toUpperCase(),
                     tableName.toUpperCase(), null)) {

            while (rs.next()) {
                TableDefinition.ColumnDefinition col = new TableDefinition.ColumnDefinition();
                col.setName(rs.getString("COLUMN_NAME"));

                // 处理Oracle特殊类型
                String typeName = rs.getString("TYPE_NAME");
                int columnSize = rs.getInt("COLUMN_SIZE");
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");

                col.setType(buildOracleTypeDefinition(typeName, columnSize, decimalDigits));
                col.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
                col.setDefaultValue(rs.getString("COLUMN_DEF"));
                col.setComment(rs.getString("REMARKS"));

                tableDef.getColumns().add(col);
            }
        }

        // 获取主键信息
        try (Connection conn = getConnection(config);
             ResultSet rs = conn.getMetaData().getPrimaryKeys(null,
                     config.getUsername().toUpperCase(),
                     tableName.toUpperCase())) {

            while (rs.next()) {
                String colName = rs.getString("COLUMN_NAME");
                tableDef.getColumns().stream()
                        .filter(c -> c.getName().equalsIgnoreCase(colName))
                        .findFirst()
                        .ifPresent(c -> c.setPrimaryKey(true));
            }
        }

        return tableDef;
    }

    private String buildOracleTypeDefinition(String typeName, int size, int scale) {
        switch (typeName) {
            case "VARCHAR2":
                return "VARCHAR2(" + size + ")";
            case "NUMBER":
                if (scale > 0) {
                    return "NUMBER(" + size + "," + scale + ")";
                } else if (size > 0) {
                    return "NUMBER(" + size + ")";
                } else {
                    return "NUMBER";
                }
            case "DATE":
                return "DATE";
            case "TIMESTAMP":
                return "TIMESTAMP";
            default:
                return typeName;
        }
    }

    @Override
    public List<String> getTableNames(DatabaseConfig config) throws SQLException {
        List<String> tables = new ArrayList<>();
        try (Connection conn = getConnection(config);
             ResultSet rs = conn.getMetaData().getTables(null,
                     config.getUsername().toUpperCase(),
                     null, new String[]{"TABLE"})) {

            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        }
        return tables;
    }

    @Override
    public void execute(DatabaseConfig config, String sql) throws SQLException {
        try (Connection conn = getConnection(config);
             Statement stmt = conn.createStatement()) {

            // Oracle需要分号分隔多个语句
            String[] statements = sql.split(";");
            for (String statement : statements) {
                if (StringUtils.isNotBlank(statement.trim())) {
                    stmt.execute(statement.trim());
                }
            }
        }
    }
}
